package com.example.dao;

import com.example.model.Department;
import com.example.model.Salary;
import com.example.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDAO {
    // 获取所有部门
    public List<Department> getAllDepartments() throws SQLException {
        List<Department> departments = new ArrayList<>();
        String sql = "SELECT * FROM departments ORDER BY dept_name";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Department dept = new Department();
                dept.setDeptId(rs.getInt("dept_id"));
                dept.setDeptName(rs.getString("dept_name"));
                dept.setManager(rs.getString("manager"));
                dept.setLocation(rs.getString("location"));
                departments.add(dept);
            }
        }
        return departments;
    }

    // 添加部门
    public void addDepartment(Department dept) throws SQLException {
        String sql = "INSERT INTO departments (dept_name, manager, location) VALUES (?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, dept.getDeptName());
            stmt.setString(2, dept.getManager());
            stmt.setString(3, dept.getLocation());
            stmt.executeUpdate();
        }
    }

    // 获取部门总数
    public int getTotalDepartments() throws SQLException {
        String sql = "SELECT COUNT(*) AS total FROM departments";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getInt("total");
            }
            return 0;
        }
    }
    // 更新部门信息
    public void updateDepartment(Department dept) throws SQLException {
        String sql = "UPDATE departments SET dept_name=?, manager=?, location=? WHERE dept_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, dept.getDeptName());
            stmt.setString(2, dept.getManager());
            stmt.setString(3, dept.getLocation());
            stmt.setInt(4, dept.getDeptId());
            stmt.executeUpdate();
        }
    }

    // 删除部门
    public void deleteDepartment(int deptId) throws SQLException {
        String sql = "DELETE FROM departments WHERE dept_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, deptId);
            stmt.executeUpdate();
        }
    }

    // 根据ID获取部门
    public Department getDepartmentById(int deptId) throws SQLException {
        String sql = "SELECT * FROM departments WHERE dept_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, deptId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Department dept = new Department();
                    dept.setDeptId(rs.getInt("dept_id"));
                    dept.setDeptName(rs.getString("dept_name"));
                    dept.setManager(rs.getString("manager"));
                    dept.setLocation(rs.getString("location"));
                    return dept;
                }
            }
        }
        return null;
    }

}

